﻿--
-- ProductBuildPlan - Returns all of the components that make up a build plan.
--   This can include components of components if components of this product in turn contain compoenents
--
CREATE function ProductBuildPlan( @ProductID int )
returns @retTable table( ProductID int, Qty int )
as
begin 
	declare @temp table( ProductID int, Qty int )
	if (select count(*) from tblProductComponent where ProductID = @ProductID) = 0 
		insert into @temp (ProductID, Qty) values ( @ProductID, 1 )
	else
	begin
		declare c1 cursor local for
			select ComponentProductID, Qty from tblProductComponent
			where ProductID = @ProductID
		declare @ComponentProductID int
		declare @Qty int
		open c1
		fetch next from c1 into @ComponentProductID, @Qty
		while @@fetch_status = 0
		begin
			insert into @temp(ProductID, Qty)
			select ProductID, @Qty * Qty from dbo.ProductBuildPlan(@ComponentProductID)

			fetch next from c1 into @ComponentProductID, @Qty
		end
		close c1
	end

	-- If this product is defined as a revision of another product then include the build plan
	-- of the parent product.
	declare @IsAProductID int
	set @IsAProductID = null
	select @IsAProductID = IsAProductID from tblProduct where ProductID = @ProductID
	if @IsAProductID is not null 
		insert into @temp(ProductID, Qty) select ProductID, Qty from dbo.ProductBuildPlan(@IsAProductID)

	insert into @retTable (ProductID, Qty) select ProductID, sum(Qty) from @temp group by ProductID

	return
end
